/**  
 * @Title: JsqlparserUtil.java
 * @Description: TODO(描述)
 * @author liuyi
 * @date 2023-10-27 09:01:43 
 */
package com.fsxgt.datagrid.ds.utils;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

import lombok.Data;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.util.TablesNamesFinder;

/**
 * @ClassName: JsqlparserUtil
 * @Description: TODO(描述)
 * @author liuyi
 * @date 2023-10-27 09:01:43
 */
public class JsqlparserUtil {

	/**
	 * 解析JOIN表达式
	 *
	 * @param joins
	 * @param expressionList
	 */
	public static void parseJoinExpression(List<Join> joins, List<Expression> expressionList) {
		if (joins == null) {
			return;
		}
		for (Join join : joins) {
			FromItem rightItem = join.getRightItem();
			if (rightItem instanceof SubJoin) {
				//parseJoinExpression(((SubJoin)rightItem).getJoinList(), expressionList);
			} else if (rightItem instanceof SubSelect) {
				SubSelect subSelect = (SubSelect)rightItem;
				SelectBody selectBody = subSelect.getSelectBody();
				parseSelectExpression(selectBody, expressionList);
			}
			Collection<Expression> list = join.getOnExpressions();

			for (Expression expression : list) {
				parseExpression(expression, expressionList);
			}
		}
	}

	/**
	 * 解析SelectBody中的所有表达式
	 *
	 * @param selectBody
	 * @param expressionList
	 */
	public static void parseSelectExpression(SelectBody selectBody, List<Expression> expressionList) {
		if (selectBody instanceof PlainSelect) {
			PlainSelect plainSelect = (PlainSelect) selectBody;

			// 处理where条件
			if (plainSelect.getWhere() != null) {
				Expression expression = plainSelect.getWhere();
				parseExpression(expression, expressionList);
				parseExpression(plainSelect.getHaving(), expressionList);
			}

			// 处理子查询
			if (plainSelect.getFromItem() != null) {
				FromItem fromItem = plainSelect.getFromItem();
				if (fromItem instanceof SubSelect) {
					SubSelect subSelect = (SubSelect) fromItem;
					SelectBody subSelectBody = subSelect.getSelectBody();
					parseSelectExpression(subSelectBody, expressionList);
				} else if (fromItem instanceof SubJoin) {
					SubJoin subJoin = (SubJoin) fromItem;
					List<Join> joins = subJoin.getJoinList();
					parseJoinExpression(joins, expressionList);
				}
			}

			// 处理join操作
			if (plainSelect.getJoins() != null) {
				List<Join> joins = plainSelect.getJoins();
				parseJoinExpression(joins, expressionList);
			}
		} else if (selectBody instanceof SetOperationList) {
			List<SelectBody> selectBodyList = ((SetOperationList) selectBody).getSelects();
			for (SelectBody selectBody1 : selectBodyList) {
				parseSelectExpression(selectBody1, expressionList);
			}

		} else {

		}
	}

	/**
	 * 查找最小表达式
	 *
	 * @param expression
	 * @param expressionList
	 */
	public static void parseExpression(Expression expression, List<Expression> expressionList) {

		
		if(expression instanceof Parenthesis) {
			expression=((Parenthesis)expression).getExpression();
		}

		// 如果是简单的查询表达式
		if (expression instanceof BinaryExpression) {
			BinaryExpression binaryExpression = (BinaryExpression) expression;

			// 左边表达式
			Expression leftExpression = binaryExpression.getLeftExpression();
			// 右边表达式
			Expression rightExpression = binaryExpression.getRightExpression();

			if (expression instanceof AndExpression || expression instanceof OrExpression) {
				parseExpression(leftExpression, expressionList);
				parseExpression(rightExpression, expressionList);
				return;
			}

			if(leftExpression instanceof InExpression || leftExpression instanceof ExistsExpression) {
				parseExpression(leftExpression, expressionList);
			}else if(rightExpression instanceof InExpression || rightExpression instanceof ExistsExpression ) {
				parseExpression(leftExpression, expressionList);
			}else if(leftExpression instanceof SubSelect ) {
				SubSelect subSelect = (SubSelect) leftExpression;
				SelectBody selectBody = subSelect.getSelectBody();
				parseSelectExpression(selectBody, expressionList);
			}else if(rightExpression instanceof SubSelect ) {
				SubSelect subSelect = (SubSelect) rightExpression;
				SelectBody selectBody = subSelect.getSelectBody();
				parseSelectExpression(selectBody, expressionList);
			}else {
				expressionList.add(expression);
			}

		} else if (expression instanceof InExpression) {
			// 处理in
			InExpression inExpression = (InExpression) expression;
			ItemsList itemsList = inExpression.getRightItemsList();
			Expression rightExpression = inExpression.getRightExpression();
			// in是列表
			if (itemsList instanceof ExpressionList) {
				expressionList.add(expression);
			} else if (rightExpression instanceof SubSelect) {// in是子查询
				SubSelect subSelect = (SubSelect) rightExpression;
				SelectBody selectBody = subSelect.getSelectBody();
				parseSelectExpression(selectBody, expressionList);
			}
		} else if (expression instanceof ExistsExpression) {
			// Between between= (Between) expression;
			ExistsExpression existsExpression = (ExistsExpression) expression;
			SubSelect subSelect = (SubSelect) existsExpression.getRightExpression();
			parseSelectExpression(subSelect.getSelectBody(), expressionList);

		}else if (expression instanceof Between) {
			// Between between= (Between) expression;
			expressionList.add(expression);
		} 
	}

	/**
	 * 查找最小表达式
	 *
	 * @param expression
	 * @param expressionList
	 */
	@Data
	public static class SQLBean {
		private String sql;
		private List<String> expressionList;
		private List<String> tables;

		public SQLBean(String sql, List<String> expressionList, List<String> tables) {
			this.sql = sql;
			this.expressionList = expressionList;
			this.tables = tables;
		}

	}

	public static SQLBean parseExpression(String sql) throws JSQLParserException {
		Select stmt = (Select) CCJSqlParserUtil.parse(sql);

		TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
		List<String> tableList = tablesNamesFinder.getTableList(stmt);

		PlainSelect plainSelect = (PlainSelect) stmt.getSelectBody();

		List list = new ArrayList();

		parseSelectExpression(plainSelect,list);
	

		List<String> expressionList = (List) list.stream().map((o) -> o.toString()).collect(Collectors.toList());

		return new JsqlparserUtil.SQLBean(plainSelect.toString(), expressionList, tableList);

	}

	public static void main(String args[]) throws JSQLParserException {

		String sql = " select max(dataNo) FROM dws.converter120_steel a join aaaa on a.aa=aaa.ccc WHERE   cast(dataNo as Int64) <=PARAM_000008  "
				+ "and tt>(select 1 from ff where uu=2) and id not in (1,2,3) and id in (select 1 from aa where b=2 and exists (select 1 from ccc where f='1' and e=3))"
				+ " group by aaa having count(a)>1";
		
		String sql2 = " select max(dataNo) FROM dws.converter120_steel  group by aaa having count(a)>1";

		List list = parseExpression("select * FROM dws.stockline_exception_dic AS a\r\n"
				+ "LEFT JOIN (SELECT ts,v,eid FROM power.collect_ironworks WHERE ts >='2023-12-13 10:41:00' AND  ts < '2023-12-13 10:42:00' AND eid = '15af4cb1' ) AS b ON a.eid = b.eid \r\n"
				+ "WHERE  a.eid = '15af4cb1')  t \r\n"
).getExpressionList();

		for (Object o : list) {
			System.out.println(o);
		}

	}

}
